'use strict';

module.exports = {
  async up(queryInterface, Sequelize) {
    // 创建用户表
    await queryInterface.createTable('Users', {
      id: {
        allowNull: false,
        autoIncrement: true,
        primaryKey: true,
        type: Sequelize.INTEGER
      },
      username: {
        type: Sequelize.STRING(50),
        allowNull: false,
        unique: true
      },
      password: {
        type: Sequelize.STRING(100),
        allowNull: false
      },
      name: {
        type: Sequelize.STRING(50),
        allowNull: false
      },
      email: {
        type: Sequelize.STRING(100),
        allowNull: false,
        unique: true
      },
      phone: {
        type: Sequelize.STRING(20),
        allowNull: true
      },
      role: {
        type: Sequelize.ENUM('admin', 'teacher', 'student'),
        defaultValue: 'student',
        allowNull: false
      },
      isActive: {
        type: Sequelize.BOOLEAN,
        defaultValue: true,
        field: 'is_active'
      },
      lastLogin: {
        type: Sequelize.DATE,
        field: 'last_login',
        allowNull: true
      },
      passwordChangedAt: {
        type: Sequelize.DATE,
        field: 'password_changed_at',
        allowNull: true
      },
      passwordResetToken: {
        type: Sequelize.STRING,
        field: 'password_reset_token',
        allowNull: true
      },
      passwordResetExpires: {
        type: Sequelize.DATE,
        field: 'password_reset_expires',
        allowNull: true
      },
      createdAt: {
        allowNull: false,
        type: Sequelize.DATE,
        field: 'created_at'
      },
      updatedAt: {
        allowNull: false,
        type: Sequelize.DATE,
        field: 'updated_at'
      },
      deletedAt: {
        type: Sequelize.DATE,
        field: 'deleted_at',
        allowNull: true
      }
    });

    // 创建课程表
    await queryInterface.createTable('Courses', {
      id: {
        allowNull: false,
        autoIncrement: true,
        primaryKey: true,
        type: Sequelize.INTEGER
      },
      code: {
        type: Sequelize.STRING(20),
        allowNull: false,
        unique: true
      },
      name: {
        type: Sequelize.STRING(100),
        allowNull: false
      },
      description: {
        type: Sequelize.TEXT,
        allowNull: true
      },
      credit: {
        type: Sequelize.FLOAT,
        allowNull: false,
        defaultValue: 2.0
      },
      capacity: {
        type: Sequelize.INTEGER,
        allowNull: false,
        defaultValue: 30
      },
      currentEnrollment: {
        type: Sequelize.INTEGER,
        field: 'current_enrollment',
        defaultValue: 0
      },
      location: {
        type: Sequelize.STRING(100),
        allowNull: true
      },
      schedule: {
        type: Sequelize.STRING(100),
        allowNull: true,
        comment: '课程时间安排，如：周一 1-2节'
      },
      status: {
        type: Sequelize.ENUM('draft', 'published', 'archived'),
        defaultValue: 'draft'
      },
      startDate: {
        type: Sequelize.DATEONLY,
        field: 'start_date',
        allowNull: true
      },
      endDate: {
        type: Sequelize.DATEONLY,
        field: 'end_date',
        allowNull: true
      },
      teacherId: {
        type: Sequelize.INTEGER,
        field: 'teacher_id',
        references: {
          model: 'Users',
          key: 'id'
        },
        onUpdate: 'CASCADE',
        onDelete: 'SET NULL'
      },
      createdAt: {
        allowNull: false,
        type: Sequelize.DATE,
        field: 'created_at'
      },
      updatedAt: {
        allowNull: false,
        type: Sequelize.DATE,
        field: 'updated_at'
      },
      deletedAt: {
        type: Sequelize.DATE,
        field: 'deleted_at',
        allowNull: true
      }
    });

    // 创建选课表
    await queryInterface.createTable('Enrollments', {
      id: {
        allowNull: false,
        autoIncrement: true,
        primaryKey: true,
        type: Sequelize.INTEGER
      },
      status: {
        type: Sequelize.ENUM('selected', 'dropped', 'completed', 'failed'),
        defaultValue: 'selected'
      },
      score: {
        type: Sequelize.FLOAT,
        allowNull: true
      },
      feedback: {
        type: Sequelize.TEXT,
        allowNull: true
      },
      enrolledAt: {
        type: Sequelize.DATE,
        field: 'enrolled_at',
        defaultValue: Sequelize.NOW
      },
      droppedAt: {
        type: Sequelize.DATE,
        field: 'dropped_at',
        allowNull: true
      },
      studentId: {
        type: Sequelize.INTEGER,
        field: 'student_id',
        references: {
          model: 'Users',
          key: 'id'
        },
        onUpdate: 'CASCADE',
        onDelete: 'CASCADE'
      },
      courseId: {
        type: Sequelize.INTEGER,
        field: 'course_id',
        references: {
          model: 'Courses',
          key: 'id'
        },
        onUpdate: 'CASCADE',
        onDelete: 'CASCADE'
      },
      createdAt: {
        allowNull: false,
        type: Sequelize.DATE,
        field: 'created_at'
      },
      updatedAt: {
        allowNull: false,
        type: Sequelize.DATE,
        field: 'updated_at'
      },
      deletedAt: {
        type: Sequelize.DATE,
        field: 'deleted_at',
        allowNull: true
      }
    });

    // 添加唯一约束，确保一个学生不能重复选同一门课
    await queryInterface.addConstraint('Enrollments', {
      fields: ['student_id', 'course_id'],
      type: 'unique',
      name: 'unique_enrollment'
    });

    // 添加索引以优化查询
    await queryInterface.addIndex('Users', ['email'], { unique: true });
    await queryInterface.addIndex('Users', ['username'], { unique: true });
    await queryInterface.addIndex('Courses', ['teacher_id']);
    await queryInterface.addIndex('Enrollments', ['student_id']);
    await queryInterface.addIndex('Enrollments', ['course_id']);
  },

  async down(queryInterface, Sequelize) {
    // 删除表时按照外键依赖顺序删除
    await queryInterface.dropTable('Enrollments');
    await queryInterface.dropTable('Courses');
    await queryInterface.dropTable('Users');
  }
};
